﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS;
using Kingdee.BOS.Core.List;
using Kingdee.BOS.Contracts;
using Kingdee.BOS.App;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using System.Data;


namespace KMXMBillService
{
    [Description("核算项目报表插件")]
    [Kingdee.BOS.Util.HotUpdate]
    public class KMXMReport : SysReportBaseService
    {
        #region 临时表定义
        //临时表集合
        private List<string> tempTable = new List<string>();
        //接存储过程的临时表
        private string T_Temp_First = string.Empty;
        //最终数据表
        private string T_Temp_Last = string.Empty;
        //自己创建的临时表的SQL语句 List
        List<SqlObject> listTableSqlObj = new List<SqlObject>();
        #endregion

        #region 报表加载初始化
        public override void Initialize()
        {
            base.Initialize();
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.ReportName = new LocaleValue("核算项目报表过滤条件查询", this.Context.UserLocale.LCID);
            this.ReportProperty.SimpleAllCols = true;
            this.ReportProperty.IsGroupSummary = true; // 支持分组汇总


            //this.ReportProperty.DecimalControlFieldList = new List<DecimalControlField>();
            //// 标示famount用famount_decimal做精度格式化
            //this.ReportProperty.DecimalControlFieldList.Add(new DecimalControlField() { ByDecimalControlFieldName = "famount", DecimalControlFieldName = "famount_decimal" });
        }
        #endregion

        /// <summary>
        /// 报表表头数据填充,在列表中用来显示给用户当前查询条件是什么
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public override Kingdee.BOS.Core.Report.ReportTitles GetReportTitles(Kingdee.BOS.Core.Report.IRptParams filter)
        {
            String  FACCTBOOKNAME, FPERIOD;
            String FSTARTYEAR, FENDYEAR, FSTARTPERIOD, FENDPERIOD;

            FACCTBOOKNAME = "";
            ReportTitles titles = new ReportTitles();
            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;


            FSTARTYEAR = Convert.ToString(dyFilter["FSTARTYEAR"]);

            FENDYEAR = Convert.ToString(dyFilter["FENDYEAR"]);

            FSTARTPERIOD = Convert.ToString(dyFilter["FSTARTPERIOD"]);

            FENDPERIOD = Convert.ToString(dyFilter["FENDPERIOD"]);

            DynamicObject FACCTBOOKID = dyFilter["FACCTBOOKID"] as DynamicObject;
            if (FACCTBOOKID != null)
            {
                FACCTBOOKNAME = Convert.ToString(FACCTBOOKID["Name"]);
            }


            FPERIOD = FSTARTYEAR + '.' + FSTARTPERIOD +'-'+FENDYEAR +'.'+FENDPERIOD;
            titles.AddTitle("FACCTBOOKNAME", FACCTBOOKNAME);
            titles.AddTitle("FPERIOD", FPERIOD);
 


            return titles;
        }

        public override Kingdee.BOS.Core.Report.ReportHeader GetReportHeaders(Kingdee.BOS.Core.Report.IRptParams filter)
        {
           
            StringBuilder sql = new StringBuilder();
            ReportHeader header = new ReportHeader();

           
            header.AddChild("FID", new LocaleValue("序号", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FAcctNumber", new LocaleValue("科目编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FAcctName", new LocaleValue("科目名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FAXMNumber", new LocaleValue("一级项目编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FAXMName", new LocaleValue("一级项目名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FBXMNumber", new LocaleValue("二级项目编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FBXMName", new LocaleValue("二级项目名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FADepNumber", new LocaleValue("一级部门编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FADepName", new LocaleValue("一级部门名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FBDepNumber", new LocaleValue("二级部门编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FBDepName", new LocaleValue("二级部门名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FCDepNumber", new LocaleValue("三级部门编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FCDepName", new LocaleValue("三级部门名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FDDepNumber", new LocaleValue("四级部门编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FDDepName", new LocaleValue("四级部门名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FEDepNumber", new LocaleValue("五级部门编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FEDepName", new LocaleValue("五级部门名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FFDepNumber", new LocaleValue("六级部门编码", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FFDepName", new LocaleValue("六级部门名称", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FQCDeAmount", new LocaleValue("期初余额(借方)", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FQCCreAmount", new LocaleValue("期初余额(贷方)", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FBQDeAmount", new LocaleValue("本期发生(借方)", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FBQCreAmount", new LocaleValue("本期发生(贷方)", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FBNDeAmount", new LocaleValue("本年累计(借方)", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FBNCreAmount", new LocaleValue("本年累计(贷方)", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FQMDeAmount", new LocaleValue("期末余额(借方)", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FQMCreAmount", new LocaleValue("期末余额(贷方)", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);


            return header;
        
        }
        public override void BuilderReportSqlAndTempTable(Kingdee.BOS.Core.Report.IRptParams filter, string tableName)
        {
            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            //创建所需临时表
            CreatBillDataFirstTempTable(filter);
            T_Temp_Last = tableName;  //获得最终返回数据临时表名
            //执行建表语句
            DBUtils.ExecuteBatch(this.Context, listTableSqlObj);
            listTableSqlObj.Clear();
            //把存储过程里的数据插入临时表里
            InsertFirstTempTable(this.Context, filter);

            //最后再把数导入到最后的返回表里
            string sSQL = @"/*dialect*/ select a.* , {0}  into {1} from " + this.T_Temp_First + " a " + " order by FID";
            KSQL_SEQ = string.Format(KSQL_SEQ, "a.FID");
            sSQL = string.Format(sSQL, this.KSQL_SEQ, tableName);
            DBUtils.Execute(this.Context, sSQL);
            this.DeleteTempTable();   //删除临时表


        }
        /// <summary>
        /// 创建临时表用来接后台存储过程传过来的数据，因为有动态列所以还需要传一下过滤条件
        /// </summary>
        private void CreatBillDataFirstTempTable(IRptParams filter)
        {
            StringBuilder sbSql = new StringBuilder();
            IDBService service = ServiceHelper.GetService<IDBService>();
            T_Temp_First = service.CreateTemporaryTableName(this.Context);
            sbSql.AppendFormat(@"CREATE TABLE {0}
                                   (FID INT default 0,
                                    FAcctNumber varchar(50) default '',
                                    FAcctName varchar(100) default '',
                                    FLEAVE INT default 0,
                                    FXMNumber varchar(100) default '',
                                    FXMName varchar(100) default '',
                                    FAXMNumber varchar(100) default '',
                                    FAXMName varchar(100) default '',
                                    FBXMNumber varchar(100) default '',
                                    FBXMName varchar(100) default '',
                                    FDepNumber varchar(100) default '',
                                    FDepName varchar(100) default '',
                                    FADepNumber varchar(100) default '',
                                    FADepName varchar(100) default '',
                                    FBDepNumber varchar(100) default '',
                                    FBDepName varchar(100) default '',
                                    FCDepNumber varchar(100) default '',
                                    FCDepName varchar(100) default '',
                                    FDDepNumber varchar(100) default '',
                                    FDDepName varchar(100) default '',
                                    FEDepNumber varchar(100) default '',
                                    FEDepName varchar(100) default '',
                                    FFDepNumber varchar(100) default '',
                                    FFDepName varchar(100) default '',
                                    FQCDeAmount decimal(19,2) default '',
                                    FQCCreAmount decimal(19,2) default '',
                                    FBQDeAmount decimal(19,2) default '',
                                    FBQCreAmount decimal(19,2) default '',
                                    FBNDeAmount decimal(19,2) default '',
                                    FBNCreAmount decimal(19,2) default '',
                                    FQMDeAmount decimal(19,2) default '',
                                    FQMCreAmount decimal(19,2) default ''

                                ) ", T_Temp_First);
            listTableSqlObj.Add(new SqlObject(sbSql.ToString(), new List<SqlParam>()));
            //记录自己创建的临时表
            tempTable.Add(T_Temp_First);
            sbSql.Clear();
        }

        /// <summary>
        /// 根据过滤条件获得执行存储过程的参数
        /// </summary>
        private string GetSpParams(IRptParams filter)
        {

            string FSYear, FSPeroid, FEYear, FEPeroid, FCURRENCYID, FPOSTED, FFORBIDSTATUS, FACCTNames, FACCOUNTBOOKID, FACCTORGID;
            string FISDETAIL, FYE, FQCFS, FBQFS, FBNFS; 
            string FLEVEL, FACCTNOS, FACCTNOE;
            string FDepLV, FDepC, FDepNOS, FDepNOE;
            string FXMLV, FXMNOS, FXMNOE;


            FCURRENCYID = "";
            FACCOUNTBOOKID = "";
            FACCTORGID = "";
            FACCTNOS = "";
            FACCTNOE = "";
            FDepNOS = "";
            FDepNOE = "";
            FXMNOS = "";
            FXMNOE = "";

            ReportTitles titles = new ReportTitles();

            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;

            FSYear = Convert.ToString(dyFilter["FSTARTYEAR"]);
            FEYear = Convert.ToString(dyFilter["FENDYEAR"]);
            FSPeroid = Convert.ToString(dyFilter["FSTARTPERIOD"]);
            FEPeroid = Convert.ToString(dyFilter["FENDPERIOD"]);

            DynamicObject FCURRENCY = dyFilter["F_PAEZ_CURRENCY"] as DynamicObject;
            if (FCURRENCY != null)
            {
                FCURRENCYID = Convert.ToString(FCURRENCY["ID"]);
            }

            FPOSTED = Convert.ToString(dyFilter["FNOTPOSTVOUCHER"]);
            if (FPOSTED == "True")
            { 
               FPOSTED="1";
            }
            else if (FPOSTED == "False")
            {
                FPOSTED = "0";
            }

            //账簿
            DynamicObject FACCOUNTBOOK = dyFilter["FACCTBOOKID"] as DynamicObject;
            if (FACCOUNTBOOK != null)
            {
                FACCOUNTBOOKID = Convert.ToString(FACCOUNTBOOK["ID"]);
                FACCTORGID = Convert.ToString(FACCOUNTBOOK["AccountOrgID_Id"]);
            }
            //明细科目
            FISDETAIL = Convert.ToString(dyFilter["FSHOWDETAILONLY"]);
            if (FISDETAIL == "True")
            {
                FISDETAIL = "1";
            }
            else if (FISDETAIL == "False")
            {
                FISDETAIL = "0";
            }
            //显示禁用科目
            FFORBIDSTATUS = Convert.ToString(dyFilter["FFORBIDBALANCE"]);
            if (FFORBIDSTATUS == "True")
            {
                FFORBIDSTATUS = "1";
            }
            else if (FFORBIDSTATUS == "False")
            {
                FFORBIDSTATUS = "0";
            }
            //显示科目全名
            FACCTNames = Convert.ToString(dyFilter["FSHOWFULLNAME"]);
            if (FACCTNames == "True")
            {
                FACCTNames = "1";
            }
            else if (FACCTNames == "False")
            {
                FACCTNames = "0";
            }
            //包括余额为0
            FYE = Convert.ToString(dyFilter["FBALANCEZER"]);
            if (FYE == "True")
            {
                FYE = "1";
            }
            else if (FYE == "False")
            {
                FYE = "0";
            }

            //期初发生
            FQCFS = Convert.ToString(dyFilter["FNOBUSINESS"]);
            if (FQCFS == "True")
            {
                FQCFS = "1";
            }
            else if (FQCFS == "False")
            {
                FQCFS = "0";
            }

            //本期发生
            FBQFS = Convert.ToString(dyFilter["FPERIODNOBALANCE"]);
            if (FBQFS == "True")
            {
                FBQFS = "1";
            }
            else if (FBQFS == "False")
            {
                FBQFS = "0";
            }
            
            //本年累计
            FBNFS = Convert.ToString(dyFilter["FYEARNOBALANCE"]);
            if (FBNFS == "True")
            {
                FBNFS = "1";
            }
            else if (FBNFS == "False")
            {
                FBNFS = "0";
            }


            ////科目
            FLEVEL = Convert.ToString(dyFilter["FBALANCELEVEL"]);

            DynamicObject FSTARTBALANCE = dyFilter["FSTARTBALANCE"] as DynamicObject;
            if (FSTARTBALANCE != null)
            {
                FACCTNOS = Convert.ToString(FSTARTBALANCE["Number"]);
            }

            DynamicObject FENDBALANCE = dyFilter["FENDBALANCE"] as DynamicObject;
            if (FENDBALANCE != null)
            {
                FACCTNOE = Convert.ToString(FENDBALANCE["Number"]);
            }
            //部门 string FDepLV, FDepC, FDepNOS, FDepNOE;
            FDepLV = Convert.ToString(dyFilter["F_PAEZ_DepLV"]);
            FDepC = Convert.ToString(dyFilter["F_PAEZ_DepC"]);
            if (FDepC == "True")
            {
                FDepC = "1";
            }
            else if (FDepC == "False")
            {
                FDepC = "0";
            }
            DynamicObject F_PAEZ_DepNOS = dyFilter["F_PAEZ_DepNOS"] as DynamicObject;
            if (F_PAEZ_DepNOS != null)
            {
                FDepNOS = Convert.ToString(F_PAEZ_DepNOS["Number"]);
            }

            DynamicObject F_PAEZ_DepNOE = dyFilter["F_PAEZ_DepNOE"] as DynamicObject;
            if (F_PAEZ_DepNOE != null)
            {
                FDepNOE = Convert.ToString(F_PAEZ_DepNOE["Number"]);
            }
            //项目 FXMLV, FXMLC, FXMNOS, FXMNOE;
            FXMLV = Convert.ToString(dyFilter["F_PAEZ_XMLV"]);

            DynamicObject F_PAEZ_XMNOS = dyFilter["F_PAEZ_XMNOS"] as DynamicObject;
            if (F_PAEZ_XMNOS != null)
            {
                FXMNOS = Convert.ToString(F_PAEZ_XMNOS["Number"]);
            }

            DynamicObject F_PAEZ_XMNOE = dyFilter["F_PAEZ_XMNOE"] as DynamicObject;
            if (F_PAEZ_XMNOE != null)
            {
                FXMNOE = Convert.ToString(F_PAEZ_XMNOE["Number"]);
            }


            return "'" + FSYear + "','" + FSPeroid + "','" + FEYear + "','" + FEPeroid + "','" + FCURRENCYID + "'," +
                   "'" + FPOSTED + "','" + FACCOUNTBOOKID + "','" + FACCTORGID + "','" + FISDETAIL + "'," +
                   "'" + FFORBIDSTATUS + "','" + FACCTNames + "','" + FYE + "','" + FQCFS + "','" + FBQFS + "','" + FBNFS + "', " +
                   "'" + FLEVEL + "','" + FACCTNOS + "','" + FACCTNOE + "'," +
                   "'" + FDepLV + "','" + FDepC + "','" + FDepNOS + "','" + FDepNOE + "'," +
                   "'" + FXMLV + "','" + FXMNOS + "','" + FXMNOE + "'";

        }
        /// <summary>
        /// 填充数据到最终的临时表里
        /// </summary>
        private void InsertFirstTempTable(Context ctx, IRptParams filter)
        {
            StringBuilder sql = new StringBuilder();

            //这个时候还没有返回表呢,先创建一下吧 

            sql.Append("/*dialect*/ INSERT INTO " + T_Temp_First);
            sql.Append("  exec SP_ZYKMBILL_SJ " + GetSpParams(filter));

            DBUtils.Execute(ctx, sql.ToString());
            sql.Clear();
        }

        /// <summary>
        /// 删除临时表操作
        /// </summary>
        public void DeleteTempTable()
        {
            IDBService service = Kingdee.BOS.App.ServiceHelper.GetService<IDBService>();
            if (this.tempTable.Count > 0)
            {
                //删除临时表
                service.DeleteTemporaryTableName(this.Context, this.tempTable.ToArray());
                this.tempTable.Clear();
            }
        }

    }
}
